﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using LotteryVote.Entity;
using System.Data.SqlClient;

namespace LotteryVote.Data
{
    public class CompanyLotteryCycleDataAccess : DataBase
    {
        public void Insert(CompanyLotteryCycle cycle)
        {
            string sql = string.Format(@"INSERT INTO {0}  ({1},{2}) VALUES (@{1},@{2}) SELECT IDENT_CURRENT('{0}')",
                CompanyLotteryCycle.TABLENAME, CompanyLotteryCycle.COMPANYID, CompanyLotteryCycle.DAYOFWEEK);
            object id = base.ExecuteScalar(sql, new SqlParameter(CompanyLotteryCycle.COMPANYID, cycle.CompanyId),
                new SqlParameter(CompanyLotteryCycle.DAYOFWEEK, (int)cycle.DayOfWeek));
            cycle.CycleId = Convert.ToInt32(id);
        }
        public void Delete(int cycleId)
        {
            string sql = string.Format(@"DELETE {0} WHERE {1}=@{1}", CompanyLotteryCycle.TABLENAME, CompanyLotteryCycle.CYCLEID);
            base.ExecuteNonQuery(sql, new SqlParameter(CompanyLotteryCycle.CYCLEID, cycleId));
        }
        public void Delete(int companyId, DayOfWeek day)
        {
            string sql = string.Format(@"DELETE {0} WHERE {1}=@{1} AND {2}=@{2}", CompanyLotteryCycle.TABLENAME, CompanyLotteryCycle.COMPANYID, CompanyLotteryCycle.DAYOFWEEK);
            base.ExecuteNonQuery(sql, new SqlParameter(CompanyLotteryCycle.COMPANYID, companyId),
                new SqlParameter(CompanyLotteryCycle.DAYOFWEEK, (int)day));
        }
        public void Clear(int companyId)
        {
            string sql = string.Format(@"DELETE {0} WHERE {1}=@{1}", CompanyLotteryCycle.TABLENAME, CompanyLotteryCycle.COMPANYID);
            base.ExecuteNonQuery(sql, new SqlParameter(CompanyLotteryCycle.COMPANYID, companyId));
        }

        public CompanyLotteryCycle GetCycle(int companyId, DayOfWeek day)
        {
            string sql = string.Format(@"SELECT * FROM {0} WHERE {1}=@{1} AND {2}=@{2}", CompanyLotteryCycle.TABLENAME,
                CompanyLotteryCycle.COMPANYID, CompanyLotteryCycle.DAYOFWEEK);
            return base.ExecuteEntity<CompanyLotteryCycle>(sql, new SqlParameter(CompanyLotteryCycle.COMPANYID, companyId),
                new SqlParameter(CompanyLotteryCycle.DAYOFWEEK, (int)day));
        }
        public IList<CompanyLotteryCycle> GetCycles(int companyId)
        {
            string sql = string.Format(@"SELECT * FROM {0} WHERE {1}=@{1}", CompanyLotteryCycle.TABLENAME, CompanyLotteryCycle.COMPANYID);
            return base.ExecuteList<CompanyLotteryCycle>(sql, new SqlParameter(CompanyLotteryCycle.COMPANYID, companyId));
        }
        /// <summary>
        /// 获取一周某天开奖的公司
        /// </summary>
        /// <param name="day">The day.</param>
        /// <returns></returns>
        public IList<LotteryCompany> GetCompanyByDay(DayOfWeek day)
        {
            string sql = string.Format(@"select lc.*,ct.* from {0} clc
join {1} lc on lc.{2}=clc.{3}
join {4} ct on ct.{5}=lc.{6}
where clc.{7}=@{7} order by lc.{8}", CompanyLotteryCycle.TABLENAME, LotteryCompany.TABLENAME,
                   LotteryCompany.COMPANYID, CompanyLotteryCycle.COMPANYID, CompanyTypeModel.TABLENAME,
                   CompanyTypeModel.ID, LotteryCompany.TYPEID, CompanyLotteryCycle.DAYOFWEEK, LotteryCompany.COMPANYID);
            return base.ExecuteList<LotteryCompany>(sql, new SqlParameter(CompanyLotteryCycle.DAYOFWEEK, (int)day));
        }
    }
}
